#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
直接使用SQL插入数据的迁移脚本
"""

import pymysql
from flask import Flask
from tools.db import init_db

def create_database():
    """创建数据库"""
    try:
        connection = pymysql.connect(
            host='localhost',
            user='root',
            password='12345',
            charset='utf8mb4'
        )
        with connection.cursor() as cursor:
            cursor.execute("CREATE DATABASE IF NOT EXISTS management_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
        connection.close()
        print("数据库 'management_system' 创建成功")
        return True
    except Exception as e:
        print(f"数据库创建失败: {str(e)}")
        return False

def direct_sql_migrate():
    """直接使用SQL插入数据"""
    try:
        print("开始直接SQL数据迁移")
        print("=" * 50)
        
        # 1. 创建数据库
        print("步骤1: 创建MySQL数据库")
        if not create_database():
            return False
        
        # 2. 初始化Flask应用
        print("\n步骤2: 初始化Flask应用")
        app = Flask(__name__)
        db = init_db(app, use_mysql=True)
        print("Flask应用初始化成功")
        
        # 3. 创建所有表
        print("\n步骤3: 创建数据库表")
        with app.app_context():
            db.create_all()
            print("数据库表创建成功")
        
        # 4. 直接使用SQL插入数据
        print("\n步骤4: 直接插入数据")
        connection = pymysql.connect(
            host='localhost',
            user='root',
            password='12345',
            database='management_system',
            charset='utf8mb4'
        )
        
        try:
            with connection.cursor() as cursor:
                # 禁用外键检查
                cursor.execute("SET FOREIGN_KEY_CHECKS = 0")
                
                # 删除并重新创建表
                print("重新创建表...")
                cursor.execute("DROP TABLE IF EXISTS user_resources")
                cursor.execute("DROP TABLE IF EXISTS role_permissions")
                cursor.execute("DROP TABLE IF EXISTS user_roles")
                cursor.execute("DROP TABLE IF EXISTS admin_user")
                cursor.execute("DROP TABLE IF EXISTS permissions")
                cursor.execute("DROP TABLE IF EXISTS resources")
                cursor.execute("DROP TABLE IF EXISTS roles")
                
                # 重新创建表
                cursor.execute("""
                CREATE TABLE roles (
                    id INT PRIMARY KEY,
                    name VARCHAR(64) UNIQUE NOT NULL,
                    description TEXT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
                """)
                
                cursor.execute("""
                CREATE TABLE resources (
                    id INT PRIMARY KEY,
                    name VARCHAR(64) NOT NULL,
                    pid INT,
                    level INT DEFAULT 1,
                    topid INT DEFAULT 0,
                    url VARCHAR(200),
                    icon VARCHAR(100),
                    sort INT DEFAULT 0,
                    status BOOLEAN DEFAULT TRUE,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (pid) REFERENCES resources(id)
                )
                """)
                
                cursor.execute("""
                CREATE TABLE permissions (
                    id INT PRIMARY KEY,
                    name VARCHAR(64) NOT NULL,
                    resource_id INT,
                    action VARCHAR(32) NOT NULL,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (resource_id) REFERENCES resources(id)
                )
                """)
                
                cursor.execute("""
                CREATE TABLE admin_user (
                    id INT PRIMARY KEY,
                    name VARCHAR(64) NOT NULL,
                    mobile VARCHAR(20) UNIQUE NOT NULL,
                    roleid INT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (roleid) REFERENCES roles(id)
                )
                """)
                
                cursor.execute("""
                CREATE TABLE user_roles (
                    user_id INT,
                    role_id INT,
                    PRIMARY KEY (user_id, role_id),
                    FOREIGN KEY (user_id) REFERENCES admin_user(id),
                    FOREIGN KEY (role_id) REFERENCES roles(id)
                )
                """)
                
                cursor.execute("""
                CREATE TABLE role_permissions (
                    role_id INT,
                    permission_id INT,
                    PRIMARY KEY (role_id, permission_id),
                    FOREIGN KEY (role_id) REFERENCES roles(id),
                    FOREIGN KEY (permission_id) REFERENCES permissions(id)
                )
                """)
                
                cursor.execute("""
                CREATE TABLE user_resources (
                    user_id INT,
                    resource_id INT,
                    PRIMARY KEY (user_id, resource_id),
                    FOREIGN KEY (user_id) REFERENCES admin_user(id),
                    FOREIGN KEY (resource_id) REFERENCES resources(id)
                )
                """)
                
                # 插入资源数据
                print("插入资源数据...")
                # 先插入根节点
                cursor.execute("INSERT INTO resources (id, name, pid, level, topid, url, icon, sort, status, created_at) VALUES (0, '根节点', NULL, 0, 0, '/', 'el-icon-home', 0, 1, NOW())")
                
                # 再插入顶级资源
                cursor.execute("INSERT INTO resources (id, name, pid, level, topid, url, icon, sort, status, created_at) VALUES (1, '销售管理', 0, 1, 0, '/sales', 'el-icon-sell', 0, 1, NOW())")
                cursor.execute("INSERT INTO resources (id, name, pid, level, topid, url, icon, sort, status, created_at) VALUES (4, '商品管理', 0, 1, 0, '/products', 'el-icon-goods', 0, 1, NOW())")
                
                # 再插入二级资源
                cursor.execute("INSERT INTO resources (id, name, pid, level, topid, url, icon, sort, status, created_at) VALUES (2, '销售订单', 1, 2, 1, '/sales/orders', 'el-icon-document', 0, 1, NOW())")
                
                # 最后插入三级资源
                cursor.execute("INSERT INTO resources (id, name, pid, level, topid, url, icon, sort, status, created_at) VALUES (3, '销售订单新增', 2, 3, 1, '/sales/orders/add', 'el-icon-plus', 0, 1, NOW())")
                cursor.execute("INSERT INTO resources (id, name, pid, level, topid, url, icon, sort, status, created_at) VALUES (5, '新增', 4, 3, 4, '/products/add', 'el-icon-plus', 0, 1, NOW())")
                
                # 插入角色数据
                print("插入角色数据...")
                roles_sql = """
                INSERT INTO roles (id, name, description, created_at) VALUES
                (1, '销售', '销售角色', NOW()),
                (2, '商品编辑', '商品编辑角色', NOW())
                """
                cursor.execute(roles_sql)
                
                # 插入权限数据
                print("插入权限数据...")
                permissions_sql = """
                INSERT INTO permissions (id, name, resource_id, action, created_at) VALUES
                (1, '销售订单新增权限', 3, 'write', NOW()),
                (2, '商品新增权限', 5, 'write', NOW())
                """
                cursor.execute(permissions_sql)
                
                # 插入用户数据
                print("插入用户数据...")
                users_sql = """
                INSERT INTO admin_user (id, name, mobile, roleid, created_at) VALUES
                (1, 'zs', '12344400001', 1, NOW()),
                (2, 'zs1', '12344400002', 1, NOW()),
                (3, 'xm', '12344400003', 2, NOW())
                """
                cursor.execute(users_sql)
                
                # 建立角色权限关系
                print("建立角色权限关系...")
                role_permissions_sql = """
                INSERT INTO role_permissions (role_id, permission_id) VALUES
                (1, 1),
                (2, 2)
                """
                cursor.execute(role_permissions_sql)
                
                # 建立用户资源关系
                print("建立用户资源关系...")
                user_resources_sql = """
                INSERT INTO user_resources (user_id, resource_id) VALUES
                (1, 3),
                (1, 5)
                """
                cursor.execute(user_resources_sql)
                
                # 重新启用外键检查
                cursor.execute("SET FOREIGN_KEY_CHECKS = 1")
                
                connection.commit()
                print("数据插入成功")
                
        finally:
            connection.close()
        
        print("\nRBAC权限系统数据迁移完成！")
        print("数据统计:")
        
        # 验证数据
        connection = pymysql.connect(
            host='localhost',
            user='root',
            password='1234',
            database='management_system',
            charset='utf8mb4'
        )
        
        try:
            with connection.cursor() as cursor:
                cursor.execute("SELECT COUNT(*) FROM admin_user")
                user_count = cursor.fetchone()[0]
                
                cursor.execute("SELECT COUNT(*) FROM roles")
                role_count = cursor.fetchone()[0]
                
                cursor.execute("SELECT COUNT(*) FROM resources")
                resource_count = cursor.fetchone()[0]
                
                cursor.execute("SELECT COUNT(*) FROM permissions")
                permission_count = cursor.fetchone()[0]
                
                print(f"  用户数量: {user_count}")
                print(f"  角色数量: {role_count}")
                print(f"  资源数量: {resource_count}")
                print(f"  权限数量: {permission_count}")
                
        finally:
            connection.close()
        
        return True
        
    except Exception as e:
        print(f"迁移失败: {str(e)}")
        import traceback
        traceback.print_exc()
        return False

if __name__ == '__main__':
    success = direct_sql_migrate()
    if success:
        print("\n数据迁移完成，可以启动应用:")
        print("   python app_with_redis.py")
    else:
        print("\n数据迁移失败")
        exit(1)
